您所在的位置：网站首页 › excel 聚合 › 005

005

2024-07-14 19:09| 来源: 网络整理| 查看: 265

数据透视表

我们希望把数据按照一定的规则聚合起来，再去做统计。比如说我们已经拥有订单记录：

订单id，用户id，创建时间，订单金额

但我们想知道以下问题的答案：

每个用户下了多少单每个月有多少订单金额

这两个问题的共同点在于，需要把原始的记录按照一定规则分组（按用户，按时间），然后再对每个分组做运算。这个时候我们就需要用到Excel里面的数据透视表了：

原始数据

生成数据透视表：

插入数据透视表

数据透视表选择字段：

每月订单金额举例来说，对于图中圈中的格子，表示创建时间为3月的所有订单，订单金额求和为300。

Excel还支持按多个维度聚合，比如我们可以在行里面增加用户ID字段：

多维度聚合

对于圈中的格子来说，表示创建时间为4月，且用户ID为4的所有订单，订单金额求和为700。

数据透视表的聚合字段可以放到行上，还可以放到列上：

多维度聚合

对于圈中的格子来说，表示创建时间为4月，且用户ID为4的所有订单，订单金额求和为700。

GROUP BY

假设数据库里面有一个订单表orders，字段为：

id, user_id, created_at, order_amount

分别表示：订单id，用户id，创建时间，订单金额。

我们想知道以下问题的答案：

每个用户下了多少单每个月有多少订单金额

分别对应的SQL为：

SELECT user_id, count(*) FROM orders GROUP BY user_id; SELECT date_format(created_at, "%Y-%m"), sum(order_amount) FROM orders GROUP BY date_format(created_at, "%Y-%m");

对于第一条SQL而言： GROUP BY user_id表示按照user_id去分组。count(*)是一个聚合函数，表示求在这个分组下所有记录的条数，也就是订单数。

对于第二条SQL而言： date_format(created_at, "%Y-%m")表示把精确到秒的时间，转化为年-月的形式，然后再去分组，这样才是我们想要的按月分组。sum(order_amount)是一个聚合函数，表示求在这个分组下所有记录的order_amount字段的和，也就是订单金额总和。

我们这里多次遇到了聚合函数这个概念。聚合函数是把GROUP BY分组后的若干记录聚合成一个数值的函数，常见的有：

count(*)，统计每个分组的记录数量 sum()，每个分组的每条记录，按照表示求值之后的总和 avg()，每个分组的每条记录，按照表示求值之后的平均值本质

对于一条SQL而言：

先按照WHERE语句筛选记录，筛选剩下的记录进入GROUP BY流程对每条记录，按照GROUP BY后面的表达式求值，相同值的划分为一组，进入SELECT流程把分组后的记录放入到SELECT，对SELECT后面的每个表达式求值，得到最终结果。

假设orders表有如下数据：

id, user_id, created_at, order_amount 1, 1, 2019-01-01 00:00:00, 100 2, 1, 2019-02-01 00:00:00, 200 3, 2, 2019-02-01 00:00:00, 300 4, 2, 2019-03-01 00:00:00, 400

我们执行以下SQL：

SELECT date_format(created_at, "%Y-%m"), sum(order_amount), count(*) FROM orders WHERE id

【本文地址】

公司简介

联系我们